CREATE TRIGGER checkNumOfSupervisor
ON Contract FOR INSERT,UPDATE AS

BEGIN
	DECLARE @companyName VARCHAR(100)
	DECLARE @pharmacyName VARCHAR(100)
	DECLARE @startDate DATETIME
	DECLARE @endDate DATETIME
    DECLARE @updatedCount INT

    SET @updatedCount = (SELECT COUNT(*) FROM INSERTED)

    IF (@updatedCount = 1)
    BEGIN
	    SET @companyName = (SELECT companyName FROM INSERTED)
	    SET @pharmacyName = (SELECT pharmacyName FROM INSERTED)
	    SET @startDate = (SELECT startDate FROM INSERTED)
	    SET @endDate = (SELECT endDate FROM INSERTED)

	    /*number of supervisor per contract > 8*/
	    IF(EXISTS(	
		    SELECT COUNT(supervisorId) AS numSupervisor, companyName, pharmacyName , startDate, endDate
		    FROM Contract 
		    WHERE companyName = @companyName 
			AND pharmacyName = @pharmacyName 
			AND startDate = @startDate 
			AND endDate = @endDate
			GROUP BY companyName, pharmacyName, startDate, endDate 
			HAVING COUNT(supervisorId) >8))
		
		BEGIN
			PRINT 'The number of different supervisor for a contract can only be at most 8.'
			ROLLBACK
		END
    END
END

